<?php
namespace ZyucMini\Core;

use ZyucMini\Core\Database;
use ZyucMini\Core\DatabaseManager;
use ZyucMini\Core\Pager;
use ZyucMini\Core\Config;

/**
 * 模型类 - 负责数据库模型操作
 * 连接数据库，实现多表查询、统计和复杂条件组合
 * 
 * @package ZyucMini\Core
 * @author 黄凯
 */
abstract class Model
{
    /**
     * 数据库连接标识
     * @var string
     */
    protected $connection = 'default';

    /**
     * 数据库实例
     * @var Database
     */
    protected $db;

    /**
     * 表名
     * @var string
     */
    protected $table;

    /**
     * 主键
     * @var string
     */
    protected $primaryKey = 'id';
    
    /**
     * 允许批量赋值的字段
     * @var array
     */
    protected $fillable = [];
    
    /**
     * 禁止批量赋值的字段
     * @var array
     */
    protected $guarded = ['id'];

    /**
     * 查询相关的参数
     * @var array
     */
    private $queryOptions = [];
    
    /**
     * WHERE条件集合
     * @var array
     */
    private $whereOptions = [];
    
    /**
     * 查询参数值
     * @var array
     */
    private $queryValues = [];

    /**
     * 最后执行的SQL语句
     * @var string
     */
    private $sql = '';

    /**
     * 构造函数
     */
    public function __construct()
    {
        // 使用连接管理器获取数据库连接，实现连接复用
        $this->db = DatabaseManager::getConnection($this->connection);
        
        if (empty($this->table)) {
            throw new \Exception("Model Error: table name is required");
        }
    }


    /**
     * 获取完整表名
     * @return string
     */
    public function getTableName()
    {
        return $this->db->getPrefix() . $this->table;
    }

    /**
     * 获取真实表名（带前缀）
     * @param string $table
     */
    private function getRealTableName($table)
    {
        if (strpos($table, $this->db->getPrefix()) === 0) {
            return $table;
        }
        return $this->db->getPrefix() . $table;
    }

    /**
     * 根据ID查找记录
     * @param mixed $id 主键值
     * @return array|null
     */
    public function find($id)
    {
        $this->newQuery();
        $this->where($this->primaryKey, '=', $id);
        return $this->first();
    }


    /**
     * 创建新的查询实例
     * @return Model
     */
    public function newQuery()
    {
        $this->queryOptions = [];
        $this->whereOptions = [];
        $this->queryValues = [];
        $this->sql = '';
        return $this;
    }

    /**
     * 设置查询字段
     * @param string|array $fields 字段名
     * @return Model
     */
    public function fields($fields)
    {
        if (is_array($fields)) {
            $fields = implode(', ', $this->validateFields($fields));
        }
        $this->queryOptions['fields'] = $fields;
        return $this;
    }

    /**
     * 创建新记录
     * @param array $data 数据
     * @return int 插入的ID
     * @throws \Exception
     */
    public function create($data)
    {
        $data = $this->filterFillable($data);
        
        if (empty($data)) {
            throw new \Exception("Model Error: no fillable data provided");
        }
        
        $fields = implode(', ', $this->validateFields(array_keys($data)));
        $placeholders = implode(', ', array_fill(0, count($data), '?'));
        $sql = "INSERT INTO `{$this->getTableName()}` ({$fields}) VALUES ({$placeholders})";
        
        $this->db->execute($sql, array_values($data));
        return $this->db->getLastId();
    }

    /**
     * 更新记录
     * @param array $data 更新数据
     * @param string $whereRaw 原始WHERE条件
     * @param array $whereParams WHERE条件参数
     * @return bool
     * @throws \Exception
     */
    public function update($data, $whereRaw = '', $whereParams = [])
    {
        $data = $this->filterFillable($data);
        
        if (empty($data)) {
            throw new \Exception("Model Error: no fillable data provided");
        }
        
        $set = [];
        foreach ($data as $key => $value) {
            $set[] = "`{$key}` = ?";
        }
        $set = implode(', ', $set);
        $params = array_values($data);

        $sql = "UPDATE `{$this->getTableName()}` SET {$set}";
        
        if ($whereRaw) {
            $sql .= " WHERE {$whereRaw}";
            $params = array_merge($params, $whereParams);
        } else if (!empty($this->whereOptions)) {
            $whereClause = $this->parseWhere();
            if (empty($whereClause)) {
                throw new \Exception("Model Error: update without where condition is not allowed");
            }
            $sql .= " {$whereClause}";
            $params = array_merge($params, $this->queryValues);
        } else {
            throw new \Exception("Model Error: update without where condition is not allowed");
        }
        
        $this->db->execute($sql, $params);
        return true;
    }

    /**
     * 删除记录
     * @param string $whereRaw 原始WHERE条件
     * @param array $whereParams WHERE条件参数
     * @return bool
     * @throws \Exception
     */
    public function delete($whereRaw = '', $whereParams = [])
    {
        $sql = "DELETE FROM `{$this->getTableName()}`";
        $params = [];
        
        if ($whereRaw) {
            $sql .= " WHERE {$whereRaw}";
            $params = $whereParams;
        } else if (!empty($this->whereOptions)) {
            $whereClause = $this->parseWhere();
            if (empty($whereClause)) {
                throw new \Exception("Model Error: delete without where condition is not allowed");
            }
            $sql .= " {$whereClause}";
            $params = $this->queryValues;
        } else {
            throw new \Exception("Model Error: delete without where condition is not allowed");
        }
        
        $this->db->execute($sql, $params);
        return true;
    }

    /**
     * 添加JOIN条件
     * @param string $table 表名
     * @param string $first 第一个字段
     * @param string $operator 操作符
     * @param string $second 第二个字段
     * @param string $type JOIN类型
     * @return Model
     */
    public function join($table, $first, $operator, $second, $type = 'INNER')
    {
        $table = $this->getRealTableName($table);
        $join = "{$type} JOIN `{$table}` ON `{$first}` {$operator} `{$second}`";
        $this->queryOptions['joins'][] = $join;
        return $this;
    }

    /**
     * 添加排序条件
     * @param string $column 字段名
     * @param string $direction 排序方向
     * @return Model
     */
    public function orderBy($column, $direction = 'ASC')
    {
        $direction = strtoupper($direction);
        if (!in_array($direction, ['ASC', 'DESC'])) {
            throw new \InvalidArgumentException("Invalid order direction: {$direction}");
        }
        
        $this->queryOptions['order'][] = "`{$column}` {$direction}";
        return $this;
    }

    /**
     * 设置LIMIT条件
     * @param int $offset 偏移量
     * @param int|null $length 长度
     * @return Model
     */
    public function limit($offset, $length = null)
    {
        if ($length === null) {
            $this->queryOptions['limit'] = (int)$offset;
        } else {
            $this->queryOptions['limit'] = (int)$offset . ', ' . (int)$length;
        }
        return $this;
    }

    public function count()
    {
        $this->fields('COUNT(*) as total');
        return $this->col();
    }

    public function sum($column)
    {
        $this->fields("SUM($column) as total");
        return $this->col();
    }

    public function avg($column)
    {
        $this->fields("AVG($column) as total");
        return $this->col();
    }

    public function max($column)
    {
        $this->fields("MAX($column) as total");
        return $this->col();
    }

    public function min($column)
    {
        $this->fields("MIN($column) as total");
        return $this->col();
    }

    public function where($condition, $oprval, $value = null)
    {
        if (is_array($condition)) {
            // 通过数组传递多个条件
            foreach ($condition as $item) {
                // 添加单个AND条件
                if(!is_array($item) || count($item) < 2) {
                    // 至少需要2个元素，条件和值。
                    throw new \Exception("Model where function error : More where condition array item must be array with 2 or 3 elements.");
                }
                $this->addWhereCondition('_and', $item[0], $item[1], $item[2] ?? null);
            }
        } else {
            // 添加单个AND条件
            $this->addWhereCondition('_and', $condition, $oprval, $value);
        }
        return $this;
    }

    public function orWhere($condition, $oprval = null, $value = null)
    {
        if (is_array($condition)) {
            // 通过数组传递多个条件
            foreach ($condition as $item) {
                // 添加单个OR条件
                if(!is_array($item) || count($item) < 2) {
                    // 至少需要2个元素，条件和值。
                    throw new \Exception("Model where function error : More where condition array item must be array with 2 or 3 elements.");
                }
                $this->addWhereCondition('_or', $item[0], $item[1], $item[2] ?? null);
            }
        } else {
            // 添加单个OR条件
            $this->addWhereCondition('_or', $condition, $oprval, $value);
        }
        return $this;
    }

    private function addWhereCondition($type, $condition, $oprval, $value = null)
    {
        if (!isset($this->whereOptions[$type])) {
            $this->whereOptions[$type] = [];
        }
        
        // 使用更精确的条件判断，避免0值被误判为false
        $hasCondition = !empty($condition);
        $hasOprval = $oprval !== null && $oprval !== '';
        $hasValue = $value !== null;
        
        if ($hasCondition && $hasOprval && !$hasValue) {
            // 传递了条件 和 值 ， ('xxxx >= ?', 100)
            $this->whereOptions[$type][] = $condition;
            if (is_array($oprval)) {
                // 多个值组合 ， ('xxxx BETWEEN ? AND ?', 100, 200)
                foreach ($oprval as $val) {
                    $this->queryValues[] = $val;
                }
            } else {
                // 单个值
                $this->queryValues[] = $oprval;
            }
        } else if ($hasCondition && $hasOprval && $hasValue) {
            // 传递了条件、操作符 和 值 ， ('xxxx ','>=', 100)
            $this->whereOptions[$type][] = '(' . $condition . ' ' . $oprval . ' ?)';
            $this->queryValues[] = $value;
        }
    }

    /**
     * 解析WHERE条件，生成SQL片段
     */
    private function parseWhere()
    {
        if(empty($this->whereOptions)) {
            return '';
        }
        
        $allWheres = [];
        if(isset($this->whereOptions['_and'])) {
            $allWheres[] = '(' . implode(' AND ', $this->whereOptions['_and']) . ')';
        }

        if(isset($this->whereOptions['_or'])) {
            $allWheres[] = '(' . implode(' OR ', $this->whereOptions['_or']) . ')';
        }

        if(empty($allWheres)) {
            return '';
        }
        return 'WHERE ' . implode(' AND ', $allWheres);
    }

    /**
     * 构建SELECT查询语句
     * 
     * @return array
     */
    private function buildSql()
    {
        $select = $this->queryOptions['fields'] ?? '*';
        $joins = empty($this->queryOptions['joins']) ? '' : ' ' . implode(' ', $this->queryOptions['joins']);
        $whereClause = $this->parseWhere();
        $groupBy = empty($this->queryOptions['groupBy']) ? '' : ' GROUP BY ' . implode(', ', $this->queryOptions['groupBy']);
        $having = empty($this->queryOptions['having']) ? '' : ' HAVING ' . implode(' ', $this->queryOptions['having']);
        $orderBy = empty($this->queryOptions['order']) ? '' : ' ORDER BY ' . implode(', ', $this->queryOptions['order']);
        $limit = empty($this->queryOptions['limit']) ? '' : ' LIMIT ' . $this->queryOptions['limit'];

        $sql = "SELECT {$select} FROM `{$this->getTableName()}` {$joins} {$whereClause} {$groupBy} {$having} {$orderBy} {$limit}";
        $this->sql = $sql;
        return ['sql' => $sql, 'params' => $this->queryValues];
    }

    // 执行查询并返回一个结果值
    public function col()
    {
        $build = $this->buildSql();
        return $this->db->fetchColumn($build['sql'], $build['params']);
    }

    // 执行查询并返回所有结果
    public function all()
    {
        $build = $this->buildSql();
        return $this->db->fetchAll($build['sql'], $build['params']);
    }

    // 执行查询并返回第一条记录
    public function first()
    {
        $build = $this->buildSql();
        return $this->db->fetch($build['sql'], $build['params']);
    }

    // 分页查询
    public function paginate($currentPage = 1, $itemsPerPage = 10)
    {
        // 先统计总记录数
        $totalItems = $this->count();

        // 创建分页对象
        $pager = new Pager($totalItems, $currentPage, $itemsPerPage);

        // 设置LIMIT参数
        $offset = ($pager->getCurrentPage() - 1) * $itemsPerPage;
        $this->limit($offset, $itemsPerPage);

        // 执行查询
        $data = $this->all();

        return [
            'data' => $data,
            'pager' => $pager
        ];
    }

    public function getDb()
    {
        return $this->db;
    }

    public function getConnectionName()
    {
        return $this->connection;
    }

    public function getSQLParams()
    {
        return [
            'sql' => $this->sql,
            'params' => $this->queryValues
        ];
    }

    /**
     * 获取最后执行的SQL语句（带参数替换）
     * @return string
     */
    public function getLastSQL()
    {
        $sqlParams = $this->getSQLParams();
        $sql = $sqlParams['sql'];
        $params = $sqlParams['params'];
        
        foreach ($params as $param) {
            $value = is_numeric($param) ? $param : "'" . addslashes($param) . "'";
            $sql = preg_replace('/\?/', $value, $sql, 1);
        }
        return $sql;
    }
    
    /**
     * 过滤可填充字段
     * @param array $data 数据
     * @return array
     */
    protected function filterFillable($data)
    {
        if (!empty($this->fillable)) {
            return array_intersect_key($data, array_flip($this->fillable));
        }
        
        if (!empty($this->guarded)) {
            return array_diff_key($data, array_flip($this->guarded));
        }
        
        return $data;
    }
    
    /**
     * 验证字段名安全性
     * @param array $fields 字段名数组
     * @return array
     * @throws \InvalidArgumentException
     */
    protected function validateFields($fields)
    {
        $validated = [];
        foreach ($fields as $field) {
            if (!preg_match('/^[a-zA-Z_][a-zA-Z0-9_]*$/', $field)) {
                throw new \InvalidArgumentException("Invalid field name: {$field}");
            }
            $validated[] = $field;
        }
        return $validated;
    }
    
    /**
     * 批量插入
     * @param array $data 数据数组
     * @return bool
     * @throws \Exception
     */
    public function insertAll($data)
    {
        if (empty($data)) {
            throw new \Exception("Model Error: no data provided for insert");
        }
        
        $firstRow = reset($data);
        $fields = array_keys($firstRow);
        $fields = $this->validateFields($fields);
        
        $values = [];
        $params = [];
        
        foreach ($data as $row) {
            $row = $this->filterFillable($row);
            $placeholders = implode(', ', array_fill(0, count($fields), '?'));
            $values[] = "({$placeholders})";
            $params = array_merge($params, array_values($row));
        }
        
        $fieldsStr = implode(', ', $fields);
        $valuesStr = implode(', ', $values);
        
        $sql = "INSERT INTO `{$this->getTableName()}` ({$fieldsStr}) VALUES {$valuesStr}";
        
        $this->db->execute($sql, $params);
        return true;
    }
    
    /**
     * 获取表结构
     * @return array
     */
    public function getTableStructure()
    {
        return $this->db->getTableStructure($this->getTableName());
    }
    
    /**
     * 检查表是否存在
     * @return bool
     */
    public function tableExists()
    {
        return $this->db->tableExists($this->getTableName());
    }
}
